from sqlalchemy import create_engine
from sqlalchemy.sql import text

from data.account import Account
from data.region import Region
from data.connect import connect, init_db

SCRIPT = """
INSERT INTO region_types ("type") VALUES 
    ('Район'),
    ('Город'),
    ('Деревня'),
    ('Село'),
    ('Поселок'),
    ('Область'),
    ('Край'),
    ('Республика'),
   	('Страна');
<NEWSCRIPT>
INSERT INTO regions ("name", latitude, longitude, latitude_2, longitude_2, type_id)
VALUES 
    ('Россия', 60.0000, 90.0000, 60.0100, 90.0100, 9), -- Страна
    ('Беларусь', 49.0000, 32.0000, 49.0100, 32.0100, 9), -- Страна
    ('Казахстан', 53.0000, 28.0000, 53.0100, 28.0100, 9); -- Страна
<NEWSCRIPT>
INSERT INTO regions ("name", parent_region, latitude, longitude, latitude_2, longitude_2, type_id)
VALUES 
    ('Москва', 1, 55.7558, 37.6176, 55.7658, 37.6276, 2), -- Город
    ('Санкт-Петербург', 1, 59.9343, 30.3351, 59.9443, 30.3451, 2), -- Город
    ('Краснодарский край', 1, 45.0355, 38.9753, 45.0455, 38.9853, 6), -- Область
    ('Московская область', 1, 55.5043, 37.1229, 55.5143, 37.1329, 6), -- Область
    ('Ростовская область', 1, 47.2357, 39.7015, 47.2457, 39.7115, 6), -- Область
    ('Крым', 1, 45.3441, 35.3570, 45.3541, 35.3670, 8), -- Республика
    ('Воронежская область', 1, 51.6755, 39.2088, 51.6855, 39.2188, 6), -- Область
    ('Татарстан', 1, 55.7944, 49.1012, 55.8044, 49.1112, 8), -- Республика
    ('Башкортостан', 1, 54.2315, 55.3257, 54.2415, 55.3357, 8), -- Республика
    ('Самарская область', 1, 53.2020, 50.1405, 53.2120, 50.1505, 6), -- Область
    ('Астраханская область', 1, 46.3479, 48.0336, 46.3579, 48.0436, 6), -- Область
    ('Красноярский край', 1, 56.0106, 92.8526, 56.0206, 92.8626, 6), -- Край
    ('Приморский край', 1, 43.1735, 132.0114, 43.1835, 132.0214, 6), -- Край
    ('Свердловская область', 1, 56.8380, 60.5975, 56.8480, 60.6075, 6), -- Область
    ('Новосибирская область', 1, 55.0084, 82.9357, 55.0184, 82.9457, 6); -- Область
<NEWSCRIPT>    
INSERT INTO weathers (temperature, humidity, wind_speed, weather_condition, precipitation_amount, measurement_date_time, region_id)
SELECT 
    ROUND((RANDOM() * 30 - 10)::numeric, 2) AS temperature,
    ROUND((RANDOM() * 100)::numeric, 2) AS humidity,
    ROUND((RANDOM() * 20)::numeric, 2) AS wind_speed,
    CASE 
        WHEN RANDOM() < 0.25 THEN 'CLEAR'
        WHEN RANDOM() < 0.5 THEN 'CLOUDY'
        WHEN RANDOM() < 0.75 THEN 'RAIN'
        ELSE 'SNOW'
    END AS weather_condition,
    ROUND((RANDOM() * 10)::numeric, 2) AS precipitation_amount,
    CURRENT_DATE - INTERVAL '3 days' * RANDOM() AS measurement_date_time,
    r.id
FROM regions r
CROSS JOIN generate_series(1, 30);
<NEWSCRIPT>
INSERT INTO public.forecasts (date_time, temperature, weather_condition, region_id)
SELECT 
    CURRENT_DATE + INTERVAL '3 days' + INTERVAL '1 day' * ROUND(RANDOM() * 7)::int AS date_time,
    ROUND((RANDOM() * 30 - 10)::numeric, 2) AS temperature,
    CASE 
        WHEN RANDOM() < 0.25 THEN 'CLEAR'
        WHEN RANDOM() < 0.5 THEN 'CLOUDY'
        WHEN RANDOM() < 0.75 THEN 'RAIN'
        ELSE 'SNOW'
    END AS weather_condition,
    r.id
FROM regions r
CROSS JOIN generate_series(1, 30);
<NEWSCRIPT>
INSERT INTO public.weather_forecasts (weather_id, forecast_id)
SELECT 
    w.id AS weather_id,
    f.id AS forecast_id
FROM 
    weathers w
JOIN 
    forecasts f ON w.region_id = f.region_id;
"""

init_db()

engine = create_engine('postgresql+pg8000://weather_user:weather_user_password@postgres_db:5432/weather_db')

with engine.connect() as connection:
    for script in SCRIPT.split('<NEWSCRIPT>'):
        connection.execute(text(script))
        connection.commit()

with connect() as session:
    test_user = session.query(Account.email == 'test@user.ru').first()
    if not test_user:
        test_user = Account(
            first_name='Test',
            last_name='User',
            email='test@user.ru'
        )
        test_user.set_password('test_password')
        session.add(test_user)
        regions = session.query(Region).filter(Region.account_id is None).all()
        for region in regions:
            region.account_id = test_user.id
        session.commit()
